package com.nwpu.main.dao;

import com.nwpu.main.pojo.Meeting;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;

/**
 * JDBCUtil
 *
 * @author 龙虹宇
 * @version 1.0
 * @date 2023/11/1
 */
public class JDBCUtil {
    // 连接数据库的url、username、password
    private static final String URL = "jdbc:mysql:///rmi_db";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "1111";
    // PreparedStatement对象
    private static PreparedStatement pstmt;
    // 查询结果集
    private static ResultSet rs;
    // sql语句
    private static String sql;
    // 数据库连接对象
    private static Connection conn;
    // Date格式定义
    private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    // 第一次编译时创建
    static {
        try {
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过用户名查询用户信息
     *
     * @param username
     * @throws SQLException
     */
    public static void selectUserByUsername(String username) throws SQLException {
        // 查询username
        sql = "select * from user where username = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setString(1, username);
        // 执行sql
        rs = pstmt.executeQuery();
    }

    /**
     * 通过用户名和密码查询用户信息
     *
     * @param username
     * @param password
     * @throws SQLException
     */
    public static void selectUserByUsernameAndPassword(String username, String password) throws SQLException {
        // 查询是否存在当前username和password
        sql = "select * from user where username = ? and password = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        // 执行sql
        rs = pstmt.executeQuery();
    }

    /**
     * 通过用户名或参加者的用户名查询会议
     *
     * @param username
     * @param otherUsername
     * @throws SQLException
     */
    public static void selectMeetingByUsernameOrOtherUsername(String username, String otherUsername) throws SQLException {
        // 查询是否存在用户名为username的会议
        sql = "select * from meeting where username = ? or otherusername = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setString(1, username);
        pstmt.setString(2, otherUsername);
        // 执行sql
        rs = pstmt.executeQuery();
    }

    /**
     * 通过用户名查询会议
     *
     * @param username
     * @throws SQLException
     */
    public static void selectMeetingByUsername(String username) throws SQLException {
        // 查询是否存在用户名为username的会议
        sql = "select * from meeting where username = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setString(1, username);
        // 执行sql
        rs = pstmt.executeQuery();
    }

    /**
     * 通过 id查询会议
     *
     * @param meetingId
     * @throws SQLException
     */
    public static void selectMeetingById(int meetingId) throws SQLException {
        // 查询是否存在id为meetingId的会议
        sql = "select * from meeting where id = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setInt(1, meetingId);
        // 执行sql
        rs = pstmt.executeQuery();
    }

    /**
     * 通过id删除会议
     *
     * @param meetingId
     * @return  成功：>0
     *          失败：0
     * @throws SQLException
     */
    public static int deleteMeetingById(int meetingId) throws SQLException {
        // 删除id为meetingId的会议
        sql = "delete from meeting where id = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setInt(1, meetingId);
        // 执行sql
        return pstmt.executeUpdate();
    }

    /**
     * 通过用户名删除会议
     *
     * @param username
     * @return
     * @throws SQLException
     */
    public static int deleteMeetingByUsername(String username) throws SQLException {
        // 删除id为meetingId的会议
        sql = "delete from meeting where username = ?";
        // 传入sql
        pstmt = conn.prepareStatement(sql);
        // 为?赋值
        pstmt.setString(1, username);
        // 执行sql
        return pstmt.executeUpdate();
    }

    /**
     * 注册用户，并查询用户名是否已经存在
     *
     * @param username
     * @param password
     * @return  success：注册成功
     *          failure：注册失败
     *          existed：用户名已存在
     */
    public static String register(String username, String password) {
        try {
            selectUserByUsername(username);
            // 判断用户名是否已经存在
            if (rs != null && rs.next()) {
                // 用户名已经存在
                System.out.println("error：用户名" + username + "已存在");
            } else {
                // 注册用户
                sql = "insert into user(username, password) values (?, ?)";
                // 传入sql
                pstmt = conn.prepareStatement(sql);
                // 为?赋值
                pstmt.setString(1, username);
                pstmt.setString(2, password);
                // 执行sql
                if (pstmt.executeUpdate() > 0) {
                    System.out.println("info：注册成功");
                    return "success";
                } else {
                    System.out.println("error：注册失败");
                    return "failure";
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 重置结果集
            rs = null;
        }
        return "existed";
    }

    /**
     * 登录，并判断用户名和密码是否正确
     *
     * @param username
     * @param password
     * @return  登录成功：success
     *          登录失败：failure
     *          用户名或密码错误：wrong
     */
    public static String login(String username, String password) {
        try {
            selectUserByUsernameAndPassword(username, password);
            // 判断用户名和密码是否正确
            if (rs != null && rs.next()) {
                // 正确
                System.out.println("info：" + username + "已登录");
                return "success";
            } else {
                System.out.println("error：用户名或密码有误");
                return "wrong";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 重置rs
            rs = null;
        }
        System.out.println("error：" + username + "登录失败");
        return "failure";
    }

    /**
     * 创建会议
     *
     * @param username
     * @param password
     * @param otherUsername
     * @param start
     * @param end
     * @param title
     * @return  用户名或密码或参会者用户名错误：wrong
     *          会议时间冲突：timeOverlap
     *          会议创建失败：failure
     *          会议创建成功：success
     *          时间格式错误：wrongTime
     *          结束时间大于开始时间：wrongTimeValue
     */
    public static String add(String username, String password, String otherUsername, String start, String end, String title) {
        try {
            // 判断username、password、otherUsername是否存在
            selectUserByUsernameAndPassword(username, password);
            if (!rs.next()) {
                System.out.println("error：用户名或密码错误");
                return "wrong";
            }
            // 重置rs
            rs = null;
            selectUserByUsername(otherUsername);
            if (!rs.next()) {
                System.out.println("error：参会者用户名错误");
                return "wrong";
            }
            // 重置rs
            rs = null;

            // 判断是否存在时间重叠的会议
            selectMeetingByUsernameOrOtherUsername(username, otherUsername);
            if (rs != null) {
                // 将传入时间包装成Date
                Date newStart;
                Date newEnd;
                // 尝试包装
                try {
                    newStart = format.parse(start);
                    newEnd = format.parse(end);
                } catch (ParseException e) {
                    System.out.println("error：时间格式错误");
                    return "wrongTime";
                }
                // 判断start时间是否小于end时间
                if (newStart.after(newEnd)) {
                    System.out.println("error：结束时间大于开始时间");
                    return "wrongTimeValue";
                }
                // 遍历rs，判断是否存在某个会议与要创建的会议时间冲突
                while (rs.next()) {
                    Date startTime = format.parse(rs.getString(4));
                    Date endTime = format.parse(rs.getString(5));
                    if (!(newEnd.before(startTime) || newStart.after(endTime))) {
                        System.out.println("error：会议时间冲突");
                        return "timeOverlap";
                    }
                }
            }

            // 创建会议
            sql = "insert into meeting(username, otherusername, start, end, title) VALUES (?, ?, ?, ?, ?)";
            // 传入sql
            pstmt = conn.prepareStatement(sql);
            // 为?赋值
            pstmt.setString(1, username);
            pstmt.setString(2, otherUsername);
            pstmt.setString(3, start);
            pstmt.setString(4, end);
            pstmt.setString(5, title);
            // 执行sql
            if (pstmt.executeUpdate() == 0) {
                System.out.println("error：创建会议失败");
                return "failure";
            }
        } catch (SQLException | ParseException e) {
            e.printStackTrace();
        } finally {
            // 重置rs
            rs = null;
        }
        System.out.println("info：创建会议："+ title +"成功");
        return "success";
    }

    /**
     * 查询会议
     *
     * @param username
     * @param password
     * @param start
     * @param end
     * @return  用户名或密码错误：wrong
     *          时间格式错误：wrongTime
     *          未查询到会议：none
     *          查询成功：==Meeting== + result
     *          结束时间大于开始时间：wrongTimeValue
     */
    public static String query(String username, String password, String start, String end) {
        String returnStr = "";
        try {
            // 判断用户名和密码是否正确
            selectUserByUsernameAndPassword(username, password);
            if (!rs.next()) {
                System.out.println("error：用户名或密码错误");
                return "wrong";
            }
            // 重置rs
            rs = null;

            // 查询该用户参加的所有会议
            selectMeetingByUsernameOrOtherUsername(username, username);
            if (!rs.next()) {
                System.out.println("info：未查询到会议");
                return "none";
            }
            Date minTime;
            Date maxTime;
            // 尝试包装成Date
            try {
                minTime = format.parse(start);
                maxTime = format.parse(end);
            } catch (ParseException e) {
                System.out.println("error：时间格式错误");
                return "wrongTime";
            }
            // 判断开始时间是否小于结束时间
            if (minTime.after(maxTime)) {
                System.out.println("error：结束时间大于开始时间");
                return "wrongTimeValue";
            }
            // 遍历rs，查找所有在时间区间内的会议，并添加到list中
            ArrayList<Meeting> meetings = new ArrayList<>();
            if (rs != null) {
                while (rs.next()) {
                    Date startTime = format.parse(rs.getString(4));
                    Date endTime = format.parse(rs.getString(5));
                    if (startTime.after(minTime) && endTime.before(maxTime)) {
                        meetings.add(new Meeting(
                                rs.getInt(1), rs.getString(2),
                                rs.getString(3), rs.getString(4),
                                rs.getString(5), rs.getString(6)));
                    }
                }
            }
            // 输出list内容
            if (meetings.size() == 0) {
                System.out.println("info：未查询到会议");
                return "none";
            } else {
                // 对list中的内容按start时间进行升序排列
                Collections.sort(meetings, Comparator.comparing(Meeting::getStart));
            }
            // 遍历list，记录meeting信息
            returnStr += "==Meetings==\n";
            for (Meeting meeting : meetings) {
                returnStr += meeting.toString();
            }
        } catch (SQLException | ParseException e) {
            e.printStackTrace();
        } finally {
            // 重置rs
            rs = null;
        }
        System.out.println("info：查询成功");
        return returnStr;
    }

    /**
     * 删除会议
     *
     * @param username
     * @param password
     * @param meetingId
     * @return  用户名或密码错误：wrong
     *          不存在该id的会议：none
     *          会议删除失败：failure
     *          会议删除成功：success
     */
    public static String delete(String username, String password, int meetingId) {
        try {
            // 判断用户名和密码是否正确
            selectUserByUsernameAndPassword(username, password);
            if (!rs.next()) {
                System.out.println("error：用户名或密码错误");
                return "wrong";
            }
            // 重置rs
            rs = null;
            // 判断是否存在id为meetingId的会议
            selectMeetingById(meetingId);
            if (!rs.next()) {
                System.out.println("error：不存在该id的会议");
                return "none";
            }
            // 重置rs
            rs = null;

            // 删除会议
            if (deleteMeetingById(meetingId) == 0) {
                System.out.println("error：会议删除失败");
                return "failure";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 重置rs
            rs = null;
        }
        System.out.println("info：会议删除成功");
        return "success";
    }

    /**
     * 删除当前用户创建的所有会议
     *
     * @param username
     * @param password
     * @return  用户名或密码错误：wrong
     *          未查询该用户创建的会议：none
     *          删除会议失败：failure
     *          删除会议成功：success + result
     */
    public static String clear(String username, String password) {
        int count = 0;
        try {
            // 判断用户名和密码是否正确
            selectUserByUsernameAndPassword(username, password);
            if (!rs.next()) {
                System.out.println("error：用户名或密码错误");
                return "wrong";
            }
            // 重置rs
            rs = null;

            // 查询该用户创建的会议
            selectMeetingByUsername(username);
            if (!rs.next()) {
                System.out.println("info：未查询该用户创建的会议");
                return "none";
            }
            // 重置rs
            rs = null;

            // 删除该用户创建的会议
            count = deleteMeetingByUsername(username);
            if (count == 0) {
                System.out.println("error：删除会议失败");
                return "failure";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 重置rs
            rs = null;
        }
        System.out.println("info：删除会议成功");
        return "success\n" + "为您删除了" + count + "个会议";
    }

    /**
     * 关闭资源
     */
    public static void close() {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
